from elixir import *
from sqlalchemy import and_, or_
from xtreg.lib.period import period
from xtreg.lib.summary import summary
import calendar
import datetime

from sqlalchemy import func, DynamicMetaData
xplanner_metadata = DynamicMetaData()

class Stat(object):
    def __init__(self, id, value, substats=None):
        self.id = id
        self.value = value
        if substats is None:
            self.substats = []
        else:
            self.substats = substats
    def sum(self):
        self.value = sum(x.value for x in self.substats if x is not None)

class Day(Stat):
    pass
class Week(Stat):
    pass
class Month(Stat):
    pass

class Base(object):
    def __repr__(self):
        return str(self)
    def __str__(self):
        return "%d: %s" % (self.id, self.name)

class Project(Base, Entity):
    using_options(metadata=xplanner_metadata, tablename='project')
    #using_table_options(schema='xplanner')
    with_fields(
       name = Field(String(255)),
       description = Field(String())
    )
    has_many('iterations', of_kind='Iteration', inverse='project')
# | id          | int(11)      | NO   | PRI | 0       |       |
# | last_update | datetime     | YES  |     | NULL    |       |
# | name        | varchar(255) | YES  |     | NULL    |       |
# | description | text         | YES  |     | NULL    |       |
# | is_hidden   | tinyint(1)   | YES  |     | NULL    |       |

class Iteration(Base, Entity):
    using_options(metadata=xplanner_metadata, tablename='iteration')
    with_fields(
       name = Field(String(255)),
       description = Field(String()), 
       start_date = Field(DateTime()),
       end_date = Field(DateTime())
    )
    belongs_to('project', of_kind='Project')
    has_many('stories', of_kind='Story', inverse='iteration')
# | id          | int(11)      | NO   | PRI | 0       |       |
# | last_update | datetime     | YES  |     | NULL    |       |
# | project_id  | int(11)      | YES  | MUL | NULL    |       |
# | name        | varchar(255) | YES  |     | NULL    |       |
# | description | text         | YES  |     | NULL    |       |
# | start_date  | date         | YES  |     | NULL    |       |
# | end_date    | date         | YES  |     | NULL    |       |
# | status      | smallint(6)  | YES  |     | NULL    |       |
# | days_worked | double       | YES  |     | NULL    |       |

class Story(Base, Entity):
    using_options(metadata=xplanner_metadata, tablename='story')
    with_fields(
       name = Field(String(255))
    )
    has_many('tasks', of_kind='Task', inverse='story')
    belongs_to('iteration', of_kind='Iteration')
# id                       | int(11)      | NO   | PRI | 0       |       |
# last_update              | datetime     | YES  |     | NULL    |       |
# name                     | varchar(255) | YES  |     | NULL    |       |
# description              | text         | YES  |     | NULL    |       |
# iteration_id             | int(11)      | YES  | MUL | NULL    |       |
# tracker_id               | int(11)      | YES  |     | NULL    |       |
# estimated_hours          | double       | YES  |     | NULL    |       |
# priority                 | int(11)      | YES  |     | NULL    |       |
# customer_id              | int(11)      | YES  | MUL | NULL    |       |
# status                   | char(1)      | NO   |     | d       |       |
# original_estimated_hours | double       | YES  |     | NULL    |       |
# disposition              | char(1)      | NO   |     | p       |       |
# postponed_hours          | double       | YES  |     | 0       |       |
# it_start_estimated_hours | double       | YES  |     | NULL    |       |
# orderNo                  | int(11)      | YES  |     | 0       |       |

class Task(Base, Entity):
    using_options(metadata=xplanner_metadata, tablename='task')
    with_fields(
       name = Field(String(255)),
       type = Field(String(255))
    )
    belongs_to('story', of_kind='Story')
    belongs_to('acceptor', of_kind='Person')
# | id                | int(11)      | NO   | PRI | 0       |       |
# | last_update       | datetime     | YES  |     | NULL    |       |
# | name              | varchar(255) | YES  |     | NULL    |       |
# | type              | varchar(255) | YES  |     | NULL    |       |
# | description       | text         | YES  |     | NULL    |       |
# | acceptor_id       | int(11)      | YES  |     | NULL    |       |
# | created_date      | date         | YES  |     | NULL    |       |
# | estimated_hours   | double       | YES  |     | NULL    |       |
# | original_estimate | double       | YES  |     | NULL    |       |
# | is_complete       | tinyint(1)   | YES  |     | NULL    |       |
# | story_id          | int(11)      | NO   | MUL | 0       |       |
# | disposition       | char(1)      | NO   |     | p       |       |

class Person(Base, Entity):
    using_options(metadata=xplanner_metadata, tablename='person')
    with_fields(
       name = Field(String(255)),
       userId = Field(String(255))
    )
    has_many('accepted_tasks', of_kind='Task', inverse='acceptor')
    #has_many('time_entries', of_kind='TimeEntry', inverse='person1', order_by='report_date')
# | id          | int(11)      | NO   | PRI | 0       |
# | last_update | datetime     | YES  |     | NULL    |
# | name        | varchar(255) | YES  |     | NULL    |
# | email       | varchar(255) | YES  |     | NULL    |
# | phone       | varchar(255) | YES  |     | NULL    |
# | initials    | varchar(255) | YES  |     | NULL    |
# | userId      | varchar(255) | YES  | UNI | NULL    |
# | password    | varchar(255) | YES  |     | NULL    |
# | is_hidden   | tinyint(1)   | YES  |     | NULL    |
    def time_entries_in(self, year=None, month=None, week=None, dayofmonth=None):
        db, de = period(year, month, week, dayofmonth)
        TE = TimeEntry
        cond = or_(TE.c.person1_id == self.id, TE.c.person2_id == self.id)
        if db is not None and de is not None:
            cond = and_(cond, TE.c.report_date >= db, TE.c.report_date < de)
        return TE.select(cond, order_by='report_date')

    def month_calendar(self, year, month):
        te = self.time_entries_in(year=year, month=month)
        work_by_day = summary(te, key=lambda e: e.report_date.date(), value=lambda e: e.duration)
        work_by_day = dict(work_by_day)

        cal = Month((year, month), 0)
        for weeknum, weekrow in enumerate(calendar.monthcalendar(year, month)):
            week = Week(weeknum, 0)
            for day in weekrow:
                if day == 0:
                    week.substats.append(None)
                else:
                    d = datetime.date(year, month, day)
                    week.substats.append(Day(d, work_by_day.get(d, 0)))
            week.sum()
            cal.substats.append(week)
        cal.sum()
        return cal

    def week_calendar(self, year, week):
        pass
    def hours_in(self, **kw):
        return sum(te.duration for te in self.time_entries_in(**kw))

class TimeEntry(Entity):
    using_options(metadata=xplanner_metadata, tablename='time_entry')
    with_fields (
       start_time = Field(DateTime),
       end_time = Field(DateTime),
       report_date = Field(DateTime),
       duration = Field(Float)
    )
    belongs_to('person1', of_kind='Person')
    belongs_to('person2', of_kind='Person')
    belongs_to('task', of_kind='Task')

    def __repr__(self):
        return str(self)
    
    def __str__(self):
        return "TE %d: date=%s, duration=%.2f" % (self.id, self.report_date, self.duration)
# | id          | int(11)  | NO   | PRI | 0       |       |
# | last_update | datetime | YES  |     | NULL    |       |
# | start_time  | datetime | YES  |     | NULL    |       |
# | end_time    | datetime | YES  |     | NULL    |       |
# | duration    | double   | YES  |     | NULL    |       |
# | person1_id  | int(11)  | YES  |     | NULL    |       |
# | person2_id  | int(11)  | YES  |     | NULL    |       |
# | task_id     | int(11)  | YES  | MUL | NULL    |       |
# | report_date | datetime | YES  |     | NULL    |       |
# | description | text     | YES  |     | NULL    |       |

